import sqlite3

conn = sqlite3.connect('python.db')
print("Opened database successfully")
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS userinfoes (
         user  VARCHAR(20),
         password  VARCHAR(6),
         age VARCHAR(3),  
         sex VARCHAR(5),
         mobile VARCHAR(11),
         balance FLOAT(12,2))''')
c.execute('''INSERT INTO userinfoes(user,
         password,age,sex,mobile,balance)
         VALUES ('luu','123456','19','woman','12345678900',10000)''')

print("Table created successfully")
conn.commit()
conn.close()
dbstr = "D:\pythonProject\python.db"


def login():
    print('请输入你的用户名:')
    k = 3
    while k:
        global username
        username = input()
        conn = sqlite3.connect(dbstr)
        userinfoes = conn.execute(
            "SELECT * FROM userinfoes WHERE user='%s'" % username)
        j = c.execute(userinfoes)

        if username == '':
            print('用户名不能为空！')
        elif j == 0:
            k -= 1
            if k != 0:
                print('用户名错误，请重新输入：')
            else:
                print
                u'你已输错三次！'
                conn.close()
                c.close()
                exit()
        else:
            break

    print('请输入你的密码：')
    k = 3
    while k:
        password = input()
        conn = sqlite3.connect(dbstr)
        userinfoes = conn.execute(
            "SELECT * FROM userinfoes WHERE user='%s'" % username)
        j = c.execute(userinfoes)

        if password == '':
            print('密码不能为空！')
        elif j == 0:
            k -= 1
            if k != 0:
                print
                u'密码错误，请重新输入：'
            else:
                print
                u'你已输错三次！'
                conn.close()
                c.close()
                exit()
        else:
            print
            u'恭喜你，登录成功！'
            break


def enroll():
    print
    u'请输入用户名(长度<=20):'  # 用户名
    while 1:
        username = input()
        conn = sqlite3.connect(dbstr)
        userinfoes = conn.execute(
            "SELECT * FROM userinfoes WHERE user='%s'" % username)
        j = c.execute(userinfoes)  # 满足此条件的数据条数
        if j == 1:
            print('该用户名已经存在，请重新输入！')
        elif username == '':
            print('用户名不能为空！')
        else:
            break

    def judge():
        while (1):
            password = input()
            if len(password) != 6:
                print('长度不正确，请重新输入：')
            else:
                break
        return password

    while 1:
        print('请输入密码(长度=6)：')
        p = judge()
        print('请再次确认密码：')
        q = judge()
        if p != q:
            print('两次输入的密码不相等，请重新输入！')
        else:
            break

    print('请输入你的年龄：')
    while 1:
        age = input()
        if age == '':
            print('年龄不能为空，请重新输入！')
        elif len(age) > 3:
            print('请输入有效年龄！')
        else:
            break

    print('请输入你的性别(man/woman):')
    while 1:
        sex = input()
        if sex != 'man' and sex != 'woman':
            print('请输入正确信息！')
        else:
            break

    print('请输入你的电话号码：')
    while 1:
        mobile = input()
        j = 0
        for i in mobile:
            if i < '0' or i > '9':
                j += 1
        if len(mobile) != 11 or j != 0:
            print('请输入有效信息！')
        else:
            break

    sql = conn.execute("INSERT INTO userinfoes(user,password,age,sex,mobile,balance)VALUES('%s','%s','%s','%s','%s','%f')" % (
        username, p, age, sex, mobile, 5000.00))
    try:
        c.execute()
        conn.commit()
        print('注册成功，获得5000元余额！')

    except:
        conn.rollback()


def Home_page():
    print('''======欢迎使用存取款系统======
====请输入你的选项，1:登录 2:注册 3:退出===
===========================================''')


def judge1():
    Home_page()
    while 1:
        judge_1 = input()

        if judge_1 == '1':  # 登录
            login()
        elif judge_1 == '2':  # 注册
            enroll()
        elif judge_1 == '3':  # 退出
            print('感谢你的使用！')
            conn.close()
            c.close()
            exit()
        else:
            print('请输入有效数字!')


judge1()

# 用户验证优化下，整体逻辑重新思考下
